﻿using Microsoft.VisualStudio.TestTools.UnitTesting;
using System;
using System.Data.SqlClient;

namespace SIR.DAL.Tests
{
	[TestClass]
	public class SqlConnectionTest
	{
		[TestMethod]
		[SIRTestCategory(typeof(Area.Database), Area.Database.Connect, 1, typeof(SqlConnection))]
		public void Connect_ConnectionStringRequired()
		{
			// Verify the SqlConnection object requires a connecction string
			try
			{
				var connection = new SqlConnection();
				connection.Open();
				Assert.Fail(); // expect an exception before this point
			}
			catch (Exception ex)
			{
				// Expect that the connection must be specified
				Assert.AreEqual("The ConnectionString property has not been initialized.", ex.Message);
			}
		}

		[TestMethod]
		[SIRTestCategory(typeof(Area.Database), Area.Database.Connect, 2, typeof(SqlConnection))]
		public void Connect_InvalidConnectionString()
		{
			// Verify the SqlConnection object requires a valid connecction string
			try
			{
				var connection = new SqlConnection();
				connection.Open();
				Assert.Fail();// expect an exception before this point
			}
			catch (Exception ex)
			{
				// Expect that the connection must be specified
				Assert.AreEqual("The ConnectionString property has not been initialized.", ex.Message);
			}
		}

		[TestMethod]
		[SIRTestCategory(typeof(Area.Database), Area.Database.Connect, 3, typeof(SqlConnection))]
		public void Connect_FailedToConnect()
		{
			// Verify the SqlConnection object could not open a connection to a invalid connection string
			try
			{
				var connection = new SqlConnection(@"Data Source=(local);Initial Catalog=FakeDB;Integrated Security=True;Pooling=False;Connect Timeout=30");
				connection.Open();
				Assert.Fail(); // expect an exception before this point
			}
			catch (Exception ex)
			{
				// Expect that the connection could not be made
				Assert.IsTrue(ex.Message.StartsWith("Cannot open database"));
			}
		}

		[TestMethod]
		[SIRTestCategory(typeof(Area.Database), Area.Database.Connect, 4, typeof(SqlConnection))]
		public void Connect_Successful()
		{
			// Verify the SqlConnection object can open a connection with a valid connection string
			try
			{
				var connection = new SqlConnection(DataServerTest._validConnectionString);
				connection.Open();
			}
			catch (Exception)
			{
				Assert.Fail(); // fail on an exection
			}
		}

		[TestMethod]
		[SIRTestCategory(typeof(Area.Database), Area.Database.Connect, 5, typeof(SqlConnection))]
		public void Connect_AlreadyConnected()
		{
			try
			{
				var connection = new SqlConnection(DataServerTest._validConnectionString);
				connection.Open();
				connection.Open();
			}
			catch (Exception ex)
			{
				// Expect that the connection cannot be opened twice
				Assert.AreEqual("The connection was not closed. The connection's current state is open.", ex.Message);
			}
		}

		[TestMethod]
		[SIRTestCategory(typeof(Area.Database), Area.Database.Read, 1, typeof(SqlConnection))]
		public void Read_NoConnection()
		{
			try
			{
				using (var cmd = new SqlCommand("SELECT COUNT(*) FROM [dbo].[User]"))
				{
					var results = cmd.ExecuteNonQuery(); // Expect no connection
				}
			}
			catch (Exception ex)
			{
				// Expect that the connection must be specified
				Assert.IsTrue(ex.Message.EndsWith("Connection property has not been initialized."));
			}
		}

		[TestMethod]
		[SIRTestCategory(typeof(Area.Database), Area.Database.Read, 2, typeof(SqlConnection))]
		public void Read_Invalid()
		{
			try
			{
				using (var conn = new SqlConnection(DataServerTest._validConnectionString))
				{
					conn.Open();
					using (var cmd = new SqlCommand("SELECT FROM [dbo].[User]", conn))
					{
						var results = cmd.ExecuteNonQuery(); // Expect invalid query
					}
				}
			}
			catch (Exception ex)
			{
				// expect an exception matching invalid query
				Assert.IsTrue(ex.Message.StartsWith("Incorrect syntax"));
			}
		}

		[TestMethod]
		[SIRTestCategory(typeof(Area.Database), Area.Database.Read, 3, typeof(SqlConnection))]
		public void Read_NoResults()
		{
			try
			{
				using (var conn = new SqlConnection(DataServerTest._validConnectionString))
				{
					conn.Open();
					using (var cmd = new SqlCommand("SELECT TOP 0 * FROM [dbo].[User]", conn))
					{
						using (var reader = cmd.ExecuteReader())
						{
							Assert.IsFalse(reader.HasRows); // Expect no results
						}
					}
				}
			}
			catch (Exception)
			{
				// Fail on the exception
				Assert.Fail();
			}
		}

		[TestMethod]
		[SIRTestCategory(typeof(Area.Database), Area.Database.Read, 4, typeof(SqlConnection))]
		public void Read_WithResults()
		{
			try
			{
				using (var conn = new SqlConnection(DataServerTest._validConnectionString))
				{
					conn.Open();
					using (var cmd = new SqlCommand("SELECT * FROM [dbo].[User]", conn))
					{
						using (var reader = cmd.ExecuteReader())
						{
							Assert.IsTrue(reader.HasRows); // Expect results
						}
					}
				}
			}
			catch (Exception)
			{
				// Fail on the exception
				Assert.Fail();
			}
		}

		[TestMethod]
		[SIRTestCategory(typeof(Area.Database), Area.Database.Insert, 1, typeof(SqlConnection))]
		public void Insert_NoConnection()
		{
			try
			{
				using (var cmd = new SqlCommand("INSERT INTO [dbo].[User]"))
				{
					var results = cmd.ExecuteNonQuery(); // Expect connection required
				}
			}
			catch (Exception ex)
			{
				// Expect that the connection must be specified
				Assert.IsTrue(ex.Message.EndsWith("Connection property has not been initialized."));
			}
		}

		[TestMethod]
		[SIRTestCategory(typeof(Area.Database), Area.Database.Insert, 2, typeof(SqlConnection))]
		public void Insert_Invalid()
		{
			try
			{
				using (var conn = new SqlConnection(DataServerTest._validConnectionString))
				{
					conn.Open();
					using (var cmd = new SqlCommand("INSERT INTO [dbo].[User]", conn))
					{
						var results = cmd.ExecuteNonQuery(); // Expect invalid query
					}
				}
			}
			catch (Exception ex)
			{
				// expect an exception matching invalid query
				Assert.IsTrue(ex.Message.StartsWith("Incorrect syntax"));
			}
		}

		[TestMethod]
		[SIRTestCategory(typeof(Area.Database), Area.Database.Insert, 3, typeof(SqlConnection))]
		public void Insert_SingleRecord()
		{
			try
			{
				using (var conn = new SqlConnection(DataServerTest._validConnectionString))
				{
					conn.Open();
					using (var cmd = new SqlCommand("INSERT INTO [dbo].[SirSession] ([UserId], [SessionId]) VALUES (-1, \'-1\')", conn))
					{
						Assert.AreEqual(1, cmd.ExecuteNonQuery()); // Expect 1 result
					}

					using (var cmd = new SqlCommand("DELETE FROM [dbo].[SirSession] WHERE [UserId] = -1 AND [SessionId] =\'-1\'", conn))
					{
						Assert.AreEqual(1, cmd.ExecuteNonQuery()); // Expect 1 result
					}
				}
			}
			catch (Exception)
			{
				// Fail on the exception
				Assert.Fail();
			}
		}

		[TestMethod]
		[SIRTestCategory(typeof(Area.Database), Area.Database.Update, 1, typeof(SqlConnection))]
		public void Update_NoConnection()
		{
			try
			{
				using (var cmd = new SqlCommand("UPDATE [dbo].[User]"))
				{
					var results = cmd.ExecuteNonQuery(); // Expect no connection
				}
			}
			catch (Exception ex)
			{
				// Expect exception requiring a connection first
				Assert.IsTrue(ex.Message.EndsWith("Connection property has not been initialized."));
			}
		}

		[TestMethod]
		[SIRTestCategory(typeof(Area.Database), Area.Database.Update, 2, typeof(SqlConnection))]
		public void Update_Invalid()
		{
			try
			{
				using (var conn = new SqlConnection(DataServerTest._validConnectionString))
				{
					conn.Open();
					using (var cmd = new SqlCommand("UPDATE [dbo].[User]", conn))
					{
						var results = cmd.ExecuteNonQuery(); // Expect invalid query
					}
				}
			}
			catch (Exception ex)
			{
				// Expect invalid syntax for an invalid request
				Assert.IsTrue(ex.Message.StartsWith("Incorrect syntax"));
			}
		}

		[TestMethod]
		[SIRTestCategory(typeof(Area.Database), Area.Database.Update, 3, typeof(SqlConnection))]
		public void Update_NoRecords()
		{

			try
			{
				using (var conn = new SqlConnection(DataServerTest._validConnectionString))
				{
					conn.Open();

					using (var cmd = new SqlCommand("UPDATE [dbo].[SirSession] SET [UserId] = -1 WHERE [UserId] = -1 AND [SessionId] = \'Update_NoRecords\'", conn))
					{
						Assert.AreEqual(0, cmd.ExecuteNonQuery()); // Expect no results
					}
				}
			}
			catch (Exception ex)
			{
				// Fail on the exception
				Assert.Fail(ex.Message);
			}
		}

		[TestMethod]
		[SIRTestCategory(typeof(Area.Database), Area.Database.Update, 4, typeof(SqlConnection))]
		public void Update_SingleRecord()
		{
			try
			{
				using (var conn = new SqlConnection(DataServerTest._validConnectionString))
				{
					conn.Open();
					using (var cmd = new SqlCommand("INSERT INTO [dbo].[SirSession] ([UserId], [SessionId]) VALUES (-1, \'Update_SingleRecord\')", conn))
					{
						Assert.AreEqual(1, cmd.ExecuteNonQuery()); // Expect 1 result
					}

					using (var cmd = new SqlCommand("UPDATE [dbo].[SirSession] SET [UserId] = -1 WHERE [UserId] = -1 AND [SessionId] =\'Update_SingleRecord\'", conn))
					{
						Assert.AreEqual(1, cmd.ExecuteNonQuery()); // Expect 1 result
					}

					using (var cmd = new SqlCommand("DELETE FROM [dbo].[SirSession] WHERE [UserId] = -1 AND [SessionId] =\'Update_SingleRecord\'", conn))
					{
						Assert.AreEqual(1, cmd.ExecuteNonQuery()); // Expect 1 result
					}
				}
			}
			catch (Exception ex)
			{
				// Fail on the exception
				Assert.Fail(ex.Message);
			}
		}

		[TestMethod]
		[SIRTestCategory(typeof(Area.Database), Area.Database.Delete, 1, typeof(SqlConnection))]
		public void Delete_NoConnection()
		{
			try
			{
				using (var cmd = new SqlCommand("DELETE [dbo].[User] WHERE"))
				{
					var results = cmd.ExecuteNonQuery(); // Expect no connection
				}
			}
			catch (Exception ex)
			{
				// Fail on the exception
				Assert.IsTrue(ex.Message.EndsWith("Connection property has not been initialized."));
			}
		}

		[TestMethod]
		[SIRTestCategory(typeof(Area.Database), Area.Database.Delete, 2, typeof(SqlConnection))]
		public void Delete_Invalid()
		{
			try
			{
				using (var conn = new SqlConnection(DataServerTest._validConnectionString))
				{
					conn.Open();
					using (var cmd = new SqlCommand("DELETE [dbo].[User] WHERE", conn))
					{
						var results = cmd.ExecuteNonQuery(); // Expect invalid query
					}
				}
			}
			catch (Exception ex)
			{
				// expect an exception matching invalid query
				Assert.IsTrue(ex.Message.StartsWith("Incorrect syntax"));
			}
		}

		[TestMethod]
		[SIRTestCategory(typeof(Area.Database), Area.Database.Delete, 3, typeof(SqlConnection))]
		public void Delete_NoRecords()
		{
			try
			{
				using (var conn = new SqlConnection(DataServerTest._validConnectionString))
				{
					conn.Open();

					using (var cmd = new SqlCommand("DELETE FROM [dbo].[SirSession] WHERE [UserId] = -1 AND [SessionId] =\'Delete_NoRecords\'", conn))
					{
						Assert.AreEqual(0, cmd.ExecuteNonQuery()); // Expect no items deleted
					}
				}
			}
			catch (Exception ex)
			{
				// Fail on the exception
				Assert.Fail(ex.Message);
			}
		}

		[TestMethod]
		[SIRTestCategory(typeof(Area.Database), Area.Database.Delete, 4, typeof(SqlConnection))]
		public void Delete_SingleRecord()
		{
			try
			{
				// Open the connection, insert a user, then delete the same user
				using (var conn = new SqlConnection(DataServerTest._validConnectionString))
				{
					conn.Open();
					using (var cmd = new SqlCommand("INSERT INTO [dbo].[SirSession] ([UserId], [SessionId]) VALUES (-1, \'Delete_SingleRecord\')", conn))
					{
						Assert.AreEqual(1, cmd.ExecuteNonQuery()); // Fail if more than one item inserted
					}

					using (var cmd = new SqlCommand("DELETE FROM [dbo].[SirSession] WHERE [UserId] = -1 AND [SessionId] =\'Delete_SingleRecord\'", conn))
					{
						Assert.AreEqual(1, cmd.ExecuteNonQuery()); // Fail if more than one item deleted
					}
				}
			}
			catch (Exception)
			{
				// Fail on the exception
				Assert.Fail();
			}
		}
	}
}
